package orm

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"strings"
)

var space = " "

type Mysql struct {
	table        string
	querySql     string
	deleteSql    string
	resultSql    string
	result       []map[string]string
	sqlType      string
	db           *sql.DB
	field        string
	where        []string
	whereKey     []string
	whereVal     []string
	wherePrepare []string
	join         string
	group        string
	having       string
	order        string
	limit        string
	union        string
	lock         string
	subquery     string
	force        string
	comment      string
}

// 初始化Mysql
func (m *Mysql) init() {
	m.table = ""
	m.querySql = "SELECT %FIELD% FROM %TABLE% %SUBQUERY% %FORCE% %JOIN% %WHERE% %GROUP% %HAVING% %ORDER% %LIMIT% %UNION% %LOCK% %COMMENT%"
	m.sqlType = ""
	m.field = "*"
	m.join = ""
	m.group = ""
	m.having = ""
	m.order = ""
	m.limit = ""
	m.union = ""
	m.lock = ""
	m.subquery = ""
	m.force = ""
	m.resultSql = ""
	m.deleteSql = "DELETE FROM %TABLE% "
	m.where = []string{}
	m.whereKey = []string{}
	m.whereVal = []string{}
	m.wherePrepare = []string{}
	m.result = []map[string]string{}
}

func (m *Mysql) Close() error {
	return m.db.Close()
}

func (m *Mysql) Table(database string) Db {
	m.init()
	m.table = database
	return m
}

func (m *Mysql) Field(rows ...string) Db {
	for _, row := range rows {
		m.field = row
	}
	return m
}

func (m *Mysql) WhereAnd(key string, condition string, val interface{}) Db {
	return m.Where(key, condition, val, "and")
}

func (m *Mysql) WhereOr(key string, condition string, val interface{}) Db {
	return m.Where(key, condition, val, "or")
}

func (m *Mysql) Where(key string, condition string, val interface{}, parallel ...string) Db {

	parallelCondition := " and "
	if len(parallel) > 0 {
		parallelCondition = space + parallel[0] + space
	}
	m.wherePrepare = append(m.wherePrepare, parallelCondition+key+" = ? ")
	m.whereKey = append(m.whereKey, key)
	switch v := val.(type) {
	case string:
		m.where = append(m.where, parallelCondition+key+space+condition+space+"'"+v+"'")
		m.whereVal = append(m.whereVal, v)
		break
	case []string:
		condition = strings.Trim(condition, space)
		if condition == "in" {
			var inVal = "("
			for _, str := range v {
				inVal += str + ","
			}
			inVal = strings.TrimRight(inVal, ",")
			inVal += ")"
			m.where = append(m.where, parallelCondition+key+space+condition+space+"'"+inVal+"'")
			m.whereVal = append(m.whereVal, inVal)
		}
		if condition == "between" && len(v) == 2 {
			inVal := v[0] + " and " + v[1]
			m.where = append(m.where, parallelCondition+key+space+condition+space+"'"+inVal+"'")
			m.whereVal = append(m.whereVal, inVal)
		}

		break
	}
	return m
}

func (m *Mysql) Limit(numbers ...string) Db {
	for i, row := range numbers {
		if i > 2 {
			break
		}
		m.limit += row + ","
	}
	m.limit = strings.TrimRight(m.limit, ",")
	return m
}

func (m *Mysql) Order(row string, sort string) Db {
	m.order = " order by " + row + space + sort
	return m
}

func (m *Mysql) Join(table string, condition string, parallel ...string) Db {
	parallelCondition := " left "
	if len(parallel) > 0 {
		parallelCondition = space + parallel[0] + space
	}
	m.join += parallelCondition + "join " + table + " on " + condition
	return m
}

func (m *Mysql) Group(row string) Db {
	m.join = " group by " + row
	return m
}

func (m *Mysql) Have(condition string) Db {
	m.having = "having " + condition
	return m
}

func (m *Mysql) InsertGetId(add map[string]string) int64 {
	res := m.insert(add)
	num, err := res.LastInsertId()
	SqlErr(err)
	return num
}

func (m *Mysql) Insert(add map[string]string) int64 {
	res := m.insert(add)
	num, err := res.RowsAffected()
	SqlErr(err)
	return num
}

func (m *Mysql) GetInsertSql(add map[string]string) string {
	var conditionResult string
	for k, v := range add {
		conditionResult += k + " = " + v + ","
	}
	conditionResult = strings.TrimRight(conditionResult, ",")
	m.resultSql = "insert into " + m.table + " set " + conditionResult
	return m.resultSql
}

func (m *Mysql) GetInsertAllSql(add []map[string]string) string {
	for _, mapV := range add {
		var conditionResult string
		for k, v := range mapV {
			conditionResult += k + " = " + v + ","
		}
		conditionResult = strings.TrimRight(conditionResult, ",")
		m.resultSql += "insert into " + m.table + " set " + conditionResult
	}

	return m.resultSql
}

func (m *Mysql) insert(add map[string]string) sql.Result {
	var conditionRead string
	var conditionVal string
	var conditionResult string
	for k, v := range add {
		conditionRead += k + "=?,"
		conditionVal += v + ","
		conditionResult += k + " = " + v + ","
	}
	conditionRead = strings.TrimRight(conditionRead, ",")
	conditionVal = strings.TrimRight(conditionVal, ",")
	conditionResult = strings.TrimRight(conditionResult, ",")
	m.resultSql += "insert into " + m.table + " set " + conditionResult
	stmt, err := m.db.Prepare("insert into " + m.table + " set " + conditionRead)
	SqlErr(err)
	res, err := stmt.Exec(conditionVal)
	SqlErr(err)
	return res
}

func (m *Mysql) InsertAll(addAll []map[string]string) int {
	var num []int64
	for _, add := range addAll {
		Id := m.InsertGetId(add)
		num = append(num, Id)
	}
	return len(num)
}

func (m *Mysql) GetDeleteSql() string {
	m.deleteSql = strings.Replace(m.deleteSql, "%TABLE%", m.table, 1)
	m.resultSql = m.deleteSql + m.getWhereKey()
	return m.resultSql
}

func (m *Mysql) Delete() int64 {
	m.deleteSql = strings.Replace(m.deleteSql, "%TABLE%", m.table, 1)
	stmt, err := m.db.Prepare(m.deleteSql + m.getWhereKey())
	SqlErr(err)
	res, err := stmt.Exec(m.whereVal)
	SqlErr(err)
	num, err := res.RowsAffected()
	SqlErr(err)
	return num
}

func (m *Mysql) getWhere() string {
	var where string
	for i, v := range m.where {
		if i == 0 && strings.Contains(v, "and") {
			where = " where " + strings.Replace(v, "and", "", 1)
		} else if i == 0 && strings.Contains(v, "or") {
			where = " where " + strings.Replace(v, "or", "", 1)
		} else {
			where += v
		}
	}
	return where
}

func (m *Mysql) getWhereKey() string {
	var where string
	for i, v := range m.wherePrepare {
		if i == 0 && strings.Contains(v, "and") {
			where = " where " + strings.Replace(v, "and", "", 1)
		} else if i == 0 && strings.Contains(v, "or") {
			where = " where " + strings.Replace(v, "or", "", 1)
		} else {
			where += v
		}
	}
	return where
}

func (m *Mysql) Update(renew map[string]string) int64 {
	var conditionRead string
	var conditionVal string
	var conditionResult string
	for k, v := range renew {
		conditionRead += k + "=?,"
		conditionVal += v + ","
		conditionResult += k + " = " + v + ","
	}
	var whereResult = m.getWhere()
	m.resultSql = "update " + m.table + " set " + conditionRead + whereResult
	conditionRead = strings.TrimRight(conditionRead, ",")
	conditionVal = strings.TrimRight(conditionVal, ",")
	conditionResult = strings.TrimRight(conditionResult, ",")
	stmt, err := m.db.Prepare("update " + m.table + " set " + conditionRead + whereResult)
	SqlErr(err)
	res, err := stmt.Exec(conditionVal, strings.Join(m.whereVal, ","))
	SqlErr(err)
	num, err := res.RowsAffected()
	SqlErr(err)
	return num
}

func (m *Mysql) Query(sql string) []map[string]string {
	m.result = []map[string]string{}
	m.querySql = sql
	m.resultSql = sql
	rows, err := m.db.Query(m.querySql)
	SqlErr(err)
	columns, err := rows.Columns()
	SqlErr(err)
	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))

	for i := range values {
		scanArgs[i] = &values[i]
	}
	fmt.Println(m.querySql)
	for rows.Next() {
		record := make(map[string]string)
		err := rows.Scan(scanArgs...)
		SqlErr(err)
		for i, col := range values {
			record[columns[i]] = string(col.([]byte))
		}
		m.result = append(m.result, record)

	}
	return m.result
}

func (m *Mysql) Select() []map[string]string {
	return m.query()
}

func (m *Mysql) query() []map[string]string {
	var where = m.getWhere()
	m.querySql = strings.Replace(m.querySql, "%TABLE%", m.table, 1)
	m.querySql = strings.Replace(m.querySql, "%FIELD%", m.field, 1)
	m.querySql = strings.Replace(m.querySql, "%SUBQUERY%", m.subquery, 1)
	m.querySql = strings.Replace(m.querySql, "%FORCE%", m.force, 1)
	m.querySql = strings.Replace(m.querySql, "%JOIN%", m.join, 1)
	m.querySql = strings.Replace(m.querySql, "%WHERE%", where, 1)
	m.querySql = strings.Replace(m.querySql, "%GROUP%", m.group, 1)
	m.querySql = strings.Replace(m.querySql, "%HAVING%", m.having, 1)
	m.querySql = strings.Replace(m.querySql, "%ORDER%", m.order, 1)
	m.querySql = strings.Replace(m.querySql, "%LIMIT%", m.limit, 1)
	m.querySql = strings.Replace(m.querySql, "%UNION%", m.union, 1)
	m.querySql = strings.Replace(m.querySql, "%LOCK%", m.lock, 1)
	m.querySql = strings.Replace(m.querySql, "%COMMENT%", m.comment, 1)
	//fmt.Println(m.querySql)
	m.resultSql = m.querySql
	return m.Query(m.querySql)
}

func (m *Mysql) Find() map[string]string {
	res := m.query()
	if len(res) > 0 {
		return m.query()[0]
	}
	return nil
}

// 连接数据库
func (m *Mysql) Connect(driverName string, dataSourceName string) error {
	var err error
	m.db, err = sql.Open(driverName, dataSourceName)
	m.sqlType = driverName
	return err
}

func SqlErr(err error) { //输出错误
	if err != nil {
		fmt.Print(err)
	}
}
